/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.CurrencyDao; 
import com.tesam.gedeon.entity.Country;
import com.tesam.gedeon.entity.Currency;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Sistema
 */
@Repository
public class CurrencyDaoImpl extends  SimpleJdbcDaoSupport implements CurrencyDao {
    
    @Autowired
    public CurrencyDaoImpl (DataSource dataSource) {
        setDataSource(dataSource);
        }

    @Override
    public List<Currency> getListByLimit(String dataToSort ,String directionOfOrder , int startLimit , int endLimit) {
        List<Currency> result = new ArrayList();
            try{
                   
                   String sql = "select cu.* , co.name as countryName from currency cu " +
                                "inner join country co on co.id = cu.countryid "
                           + "where cu.deleted = 0 " 
                        + "order by "+dataToSort+" "+directionOfOrder+" limit "+endLimit+" offset "+startLimit;
                    
 
                    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                    for (Map row : rows) {
		Currency currency = new Currency();
		currency.setId((Integer)(row.get("id")));
		currency.setName((String)row.get("name"));
                currency.setSymbol((String)row.get("symbol"));
		currency.setCode((String)row.get("code"));
                
                Country country = new Country();
                country.setName((String)row.get("countryName"));
                country.setId((Integer)row.get("countryid"));
                currency.setCountry(country);
		result.add(currency);
                }
 
	 
                   
            }catch(Exception e){
           
            }
        return result;
    }
    
    @Override
    public List<Currency> getSearchCurrencyByLimit(String dataToSort ,String directionOfOrder , int startLimit , int endLimit,Currency currency){
        List<Currency> result = new ArrayList();
             try{
                  
                   String sql = "select cu.* , co.name as countryName from currency cu " +
                          "inner join country co on co.id = cu.countryid "+ 
                          "where cu.deleted = 0 and upper(cu.name) like '%"+currency.getName().toUpperCase()+"%' " + 
                          "order by  "+dataToSort+" "+directionOfOrder+" limit "+endLimit+" offset "+startLimit;
                  
                   System.out.println(sql);
 
               List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
               for (Map row : rows) {
                     
		Currency currencyD = new Currency();
		currencyD.setId((Integer)(row.get("id")));
		currencyD.setName((String)row.get("name"));
                currencyD.setSymbol((String)row.get("symbol"));
		currencyD.setCode((String)row.get("code"));
                
                Country country = new Country();
                country.setName((String)row.get("countryName"));
                country.setId((Integer)row.get("countryid"));
                currencyD.setCountry(country);
		result.add(currencyD);
                }
 
            }catch(Exception e){
           
            }
        return result ;
    }
    
    @Override
    public int saveCurrency(Currency currency){
        
        int id = 0 ;
        
        try{
             
            getJdbcTemplate().update("insert into currency (createdBy , datecreated , status , deleted , presence , "
                    + "ownerid , code , name , symbol , countryid) values( "
                    + "? , NOW() , 1 , 0 , 0 , "
                    + "? , ? , ? , ? , ?)" , currency.getCreatedBy() , currency.getOwnerId() ,
                    currency.getCode() , currency.getName() , currency.getSymbol(),currency.getCountry().getId());
            
            id = getJdbcTemplate().queryForInt("SELECT last_value FROM entity_id_seq");
        }catch(Exception e){
        
        }
        
        return id;
    }
    
    @Override
    public int getCountAll(){
        return getJdbcTemplate().queryForInt("select count(*) as total from currency where deleted = 0");
    }
    
    @Override
    public int getTotalBySearch(Currency currency){
        System.out.println("select count(*) as total from currency where deleted = 0 and upper(name) like '%"+currency.getName()+"%'");
        return getJdbcTemplate().queryForInt("select count(*) as total from currency "
                + "where deleted = 0 and upper(name) like '%"+currency.getName()+"%'"  );
    }
    
    @Override
    public List<Currency> getCurrencyById(Currency currency){
        List<Currency> result = new ArrayList();
        
            try{
                
                getJdbcTemplate().update("update currency set viewedtime = NOW() "
                        + " where id = ? " , currency.getId());
                
                String sql = "select cu.* , co.name as countryName from currency cu " +
                                "inner join country co on co.id = cu.countryid "
                           + "where cu.deleted = 0 and cu.id = "+currency.getId() ;
                
                   List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                    for (Map row : rows) { 
		Currency currencyD = new Currency();
		currencyD.setId((Integer)(row.get("id")));
		currencyD.setName((String)row.get("name"));
                currencyD.setSymbol((String)row.get("symbol"));
		currencyD.setCode((String)row.get("code"));
                
                Country country = new Country();
                country.setName((String)row.get("countryName"));
                country.setId((Integer)row.get("countryid"));
                currencyD.setCountry(country);
		result.add(currencyD);
                
                }
            }catch(Exception e){
                System.out.println("getCurrencyById : "+e.getMessage());
            }
           
             
        
        return result;
    }
    
    @Override
    public List<Currency> updateCurrency(Currency currency){
        List<Currency> result = new ArrayList();
        
            try{
                
                getJdbcTemplate().update("update currency set modifiedby = ? , datemodified = NOW() ,"
                        + "code = ? , name = ? , symbol = ? , countryid = ?   where id = ?",currency.getModifiedBy() , 
                        currency.getCode() , currency.getName() ,currency.getSymbol() , 
                         currency.getCountry().getId() , currency.getId());
                
                result = this.getCurrencyById(currency);
                
            }catch(Exception e){
            
            }
        
        return result ;
    }
    
    @Override
    public void deleteCurrency(Currency currency){
         
        getJdbcTemplate().update("update currency set deleted = 1 where id = ?" , currency.getId());
    }
}
